>Could you give an actual example?
Sure. The idea is that 'objects' (persons, books, places) from a
table obj are linked with each other in a link table ool where
objects from obj can appear in an 'subject' or a 'object' column.
Since the relation can be any, this is a very flexible and lean
design for an 'ontology-like' collection of facts.
As I said, the CREATE VIEW returns an error (column obj_id
duplicated), while the select statement by itself works (with an
additional constraint on s.obj_id, otherwise the db would throw up
hundreds of thousands of rows).
CREATE VIEW relations_aspect_subject AS
SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id,
o.canonical_name
FROM obj s, obj o, ool, rlt
WHERE s.obj_id = ool.subject
AND o.obj_id = ool.object
AND rlt.rlt_id = ool.relation
ORDER BY rlt_id
Do I miss something obvious?
thanks,
Christoph